SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO

/*===================================BEGIN=================================*/

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROC spASSIGN_CONTACT_TO_TEAMS
	@CONTACT_ID_LIST NVARCHAR(max),
	@TEAM_ID_LIST NVARCHAR(MAX),
	@ASSIGN_TYPE CHAR(1),
	@TRANSACTION_ID UNIQUEIDENTIFIER OUT
AS
BEGIN
	--Get Transaction id
	SELECT @TRANSACTION_ID = NEWID()

	--Return if no contact or no team
 	IF (LEN(@CONTACT_ID_LIST) <= 0) RETURN;
 	IF (LEN(@TEAM_ID_LIST) <= 0) RETURN;
	
	--Add , add end if have no character
	IF ((SELECT CHARINDEX(',', @CONTACT_ID_LIST)) <= 0)
	BEGIN
		SELECT @CONTACT_ID_LIST = @CONTACT_ID_LIST + ','
	END
	IF ((SELECT CHARINDEX(',', @TEAM_ID_LIST)) <= 0)
	BEGIN
		SELECT @TEAM_ID_LIST = @TEAM_ID_LIST + ','
	END
	
	--Declare variables
	DECLARE @BeginIndex INT = 0
	DECLARE @SplitIndex INT = (SELECT CHARINDEX(',', @CONTACT_ID_LIST))
	DECLARE @Priority INT = 0
	DECLARE @ProcessedId UNIQUEIDENTIFIER
	DECLARE @CONTACT_ID_TABLE TABLE (CONTACT_ID UNIQUEIDENTIFIER, PRIORITY INT)
	DECLARE @TEAM_ID_TABLE TABLE (TEAM_ID UNIQUEIDENTIFIER, PRIORITY INT)
	
	--Get contact id table
	WHILE (@SplitIndex > 0)
	BEGIN
		SELECT @ProcessedId = CONVERT(UNIQUEIDENTIFIER,SUBSTRING(@CONTACT_ID_LIST, @BeginIndex, @SplitIndex))
		
		SELECT @Priority = @Priority + 1
		INSERT INTO @CONTACT_ID_TABLE VALUES  ( @ProcessedId, @Priority)
		
		SELECT @BeginIndex = @SplitIndex + 1
		SELECT @SplitIndex = CHARINDEX(',', @CONTACT_ID_LIST, @SplitIndex + 1)
	END
	
	--Redeclare variables
	SELECT @BeginIndex = 0
	SELECT @SplitIndex =  (SELECT CHARINDEX(',', @TEAM_ID_LIST))
	SELECT @Priority = 0
	
	--Get team id table
	WHILE (@SplitIndex > 0)
	BEGIN
		SELECT @ProcessedId = CONVERT(UNIQUEIDENTIFIER,SUBSTRING(@TEAM_ID_LIST, @BeginIndex, @SplitIndex))
		
		SELECT @Priority = @Priority + 1
		INSERT INTO @TEAM_ID_TABLE VALUES ( @ProcessedId, @Priority)
		
		SELECT @BeginIndex = @SplitIndex + 1
		SELECT @SplitIndex = CHARINDEX(',', @TEAM_ID_LIST, @SplitIndex + 1)
	END
	
	--Get Total contacts & Total teams
	DECLARE @TotalContact INT = 0
	SELECT @TotalContact = COUNT(*) FROM @CONTACT_ID_TABLE
	DECLARE @TotalTeam INT = 0
	SELECT @TotalTeam = COUNT(*) FROM @TEAM_ID_TABLE

	IF (@TotalContact = 0 OR @TotalTeam = 0) RETURN
	
	--Begin assign
	DECLARE @ContactEachTeam INT = @TotalContact / @TotalTeam
	DECLARE @LeftContact INT = @TotalContact - (@ContactEachTeam * @TotalTeam)
	DECLARE @CurrentContactIndex INT = 0
	DECLARE @CurrentTeamIndex INT = 0
	DECLARE @CurrentRemainContact INT
	DECLARE @CurrentTeamId UNIQUEIDENTIFIER
	DECLARE @CurrentContactId UNIQUEIDENTIFIER
	
	--Backup old assignation
	UPDATE dbo.TEAMS_CONTACT_ASSIGNATION
	SET REPLACE_TRANSACTION_ID = @TRANSACTION_ID
	WHERE REPLACE_TRANSACTION_ID IS NULL
	AND CAMPAIGN_CONTACT_ID IN (SELECT CONTACT_ID FROM @CONTACT_ID_TABLE)
	
	--If contact < team -> all assign to team 1
	IF (@ContactEachTeam = 0)
	BEGIN
		SELECT @CurrentTeamId = (SELECT TOP 1 TEAM_ID FROM @TEAM_ID_TABLE t WHERE t.PRIORITY = 1)
		INSERT INTO dbo.TEAMS_CONTACT_ASSIGNATION ( 
			ID , TEAM_ID ,CAMPAIGN_CONTACT_ID ,TEAM_MEMBER_ID ,
			ASSIGN_TO_TEAM_DATE ,ASSIGN_TO_MEMBER_DATE ,TRANSACTION_ID
		)
		SELECT NEWID() 'ID', @CurrentTeamId 'TEAM_ID', c.CONTACT_ID 'CAMPAIGN_CONTACT_ID', NULL 'TEAM_MEMBER_ID',
			GETDATE() 'ASSIGN_TO_TEAM_DATE', NULL 'ASSIGN_TO_MEMBER_DATE', @TRANSACTION_ID 'TRANSACTION_ID' 
		FROM @CONTACT_ID_TABLE c
		ORDER BY c.PRIORITY
	END
	ELSE
	--If contact > team -> assign equal, the remain ones will be assigned to first team
	BEGIN
		SELECT @CurrentRemainContact = @ContactEachTeam
		SELECT @CurrentTeamIndex = 1
		WHILE (@CurrentContactIndex < (@ContactEachTeam * @TotalTeam))
		BEGIN
			SELECT @CurrentContactIndex = @CurrentContactIndex + 1
			SELECT @CurrentTeamId = (SELECT TOP 1 TEAM_ID FROM @TEAM_ID_TABLE t WHERE t.PRIORITY = @CurrentTeamIndex)
			SELECT @CurrentContactId = (SELECT TOP 1 CONTACT_ID FROM @CONTACT_ID_TABLE c WHERE c.PRIORITY = @CurrentContactIndex)
		
			INSERT INTO dbo.TEAMS_CONTACT_ASSIGNATION ( 
				ID , TEAM_ID ,CAMPAIGN_CONTACT_ID ,TEAM_MEMBER_ID ,
				ASSIGN_TO_TEAM_DATE ,ASSIGN_TO_MEMBER_DATE ,TRANSACTION_ID
			) VALUES (
				NEWID(), @CurrentTeamId, @CurrentContactId, NULL,
				GETDATE(), NULL, @TRANSACTION_ID
			)
			--Set currentRemainContact & Reset if equal 0
			SELECT @CurrentRemainContact = @CurrentRemainContact - 1
			IF (@CurrentRemainContact <= 0)
			BEGIN
				SELECT @CurrentRemainContact = @ContactEachTeam
				SELECT @CurrentTeamIndex = @CurrentTeamIndex + 1
			END
		END
		
		--Assign left contacts after equal assign to first team
		IF (@LeftContact > 0)
		BEGIN
			SELECT @CurrentTeamId = (SELECT TOP 1 TEAM_ID FROM @TEAM_ID_TABLE t WHERE t.PRIORITY = 1)
			INSERT INTO dbo.TEAMS_CONTACT_ASSIGNATION ( 
				ID , TEAM_ID ,CAMPAIGN_CONTACT_ID ,TEAM_MEMBER_ID ,
				ASSIGN_TO_TEAM_DATE ,ASSIGN_TO_MEMBER_DATE ,TRANSACTION_ID
			)
			SELECT NEWID() 'ID', @CurrentTeamId 'TEAM_ID', c.CONTACT_ID 'CAMPAIGN_CONTACT_ID', NULL 'TEAM_MEMBER_ID',
				GETDATE() 'ASSIGN_TO_TEAM_DATE', NULL 'ASSIGN_TO_MEMBER_DATE', @TRANSACTION_ID 'TRANSACTION_ID' 
			FROM @CONTACT_ID_TABLE c
			WHERE PRIORITY > (@ContactEachTeam * @TotalTeam)
		END
	END
	
	
END


/*===================================END=================================*/


IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO

IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
PRINT 'The database update succeeded'

-- Record the script update
INSERT INTO dbo.[SchemaChanges]
       ([MajorReleaseNumber]
       ,[MinorReleaseNumber]
       ,[BuildReleaseNumber]
       ,[RevisionReleaseNumber]
       ,[ScriptName]
       ,[DateApplied]
       ,[Description])
VALUES
       ('1'
       ,'00'
       ,'01'
       ,'006'
       ,'Upgrade-1.00.01.009.sql'	   
       ,GETDATE()
	   ,'Create proc assign contact to team')
	   COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors
GO        

